IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_cs_ResourceLinks_Post]')) -- 2005 thing AND parent_id = OBJECT_ID(N'[dbo].[cs_ResourceLinks_Post]')
ALTER TABLE [dbo].[cs_ResourceLinks_Post] DROP CONSTRAINT [FK_cs_ResourceLinks_Post]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinks_GetAllDead]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinks_GetAllDead]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkMoveLink]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkMoveLink]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkMarkDead]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkMarkDead]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkModerationSummary]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkModerationSummary]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkLink_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkLink_InsertUpdate]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkBreadCrumbGet]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkBreadCrumbGet]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkComment_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkComment_InsertUpdate]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkComment_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkComment_Delete]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkSection_GetLinks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkSection_GetLinks]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkSection_GetLinkComments]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkSection_GetLinkComments]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkSection_GetLink]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkSection_GetLink]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkSection_Get]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkSection_Get]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkEmails_TrackingSection]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkEmails_TrackingSection]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLink_Search]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLink_Search]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLink_Search_PostReindex]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLink_Search_PostReindex]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLink_Sections_GetAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLink_Sections_GetAll]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkLink_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkLink_Delete]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkDeadLinksSummary]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkDeadLinksSummary]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkUpdateLinkHitCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkUpdateLinkHitCount]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResourceLinks_Post]') AND type in (N'U'))
DROP TABLE [dbo].[cs_ResourceLinks_Post]



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkModerationSummary]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[cs_ResLinkModerationSummary]
(
	@SettingsId int,
	@ApplicationType int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

--I''m sure there is a better way of doing this???
create table #results
(
	SectionID int,
	[Name] nvarchar(256),
	ParentID int,
	PostId int
)

INSERT INTO #results (ParentID ,PostId , SectionID ,[Name] ) (
SELECT cs_Posts.ParentID , cs_posts.PostID , cs_Sections.SectionID, cs_Sections.Name
	FROM   cs_Posts INNER JOIN
							cs_Sections ON cs_Posts.SettingsID = cs_Sections.SettingsID AND cs_Posts.SectionID = cs_Sections.SectionID
	WHERE (cs_Posts.IsApproved = 0 AND @SettingsId = cs_Sections.SettingsID AND cs_Sections.ApplicationType = @ApplicationType)
	GROUP BY cs_Sections.SectionID, cs_Sections.Name,cs_Posts.ParentID ,cs_posts.PostID)


create table #results2
(
	LinkCount int,
	CommentCount int,
	SectionID int PRIMARY KEY,
	[Name] nvarchar(256)
)
	

INSERT INTO #results2 (LinkCount ,SectionID ,[Name] )(SELECT Count(*),SectionID ,[name] from #results where ParentID = 0 GROUP BY SectionID,[name])

UPDATE #results2 SET CommentCount = coalesce((SELECT Count(*) as CommentCount from #results where ParentID <> 0 AND #results2.SectionID=SectionID GROUP BY SectionID,[name]) , 0)

SELECT * FROM #results2

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkModerationSummary] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLink_Search]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE    procedure [dbo].[cs_ResLink_Search] (
	@SearchSQL NText,
	--@RecordCountSQL nvarchar(4000),
	@PageIndex int = 0,
	@PageSize int = 25,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	DECLARE @StartTime datetime
	DECLARE @RowsToReturn int
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @Count int
	DECLARE @TotalRecords int

	-- Used to calculate cost of query
	SET @StartTime = GetDate()

	-- Set the rowcount
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)
	--SET ROWCOUNT @RowsToReturn

	-- Calculate the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1

	-- Create a temp table to store the results in
	CREATE TABLE #SearchResults
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		PostID int,
		SectionID int,
		Weight int,
		PostDate datetime
	)

	-- Fill the temp table
	INSERT INTO #SearchResults (PostID, SectionID, Weight, PostDate)
	exec (@SearchSQL)

	SET @TotalRecords = @@rowcount

	SET ROWCOUNT @RowsToReturn

	-- SELECT actual search results from this table
	SELECT
		Posts.UserID , Thread.SectionID , Posts.PostID , Posts.TotalViews , Posts.ThreadID , Posts.Subject , Posts.Body , Posts.PostAuthor , Posts.PostDate , Posts.FormattedBody , Posts.IsApproved , LinkedTbl.LinkURL , LinkedTbl.ReportedDead , LinkedTbl.ReportingUserID ,
		Posts.PropertyNames , Posts.PropertyValues , Thread.MostRecentPostAuthor , Thread.MostRecentPostAuthorID , Thread.RatingSum , Thread.TotalRatings , 
		(SELECT Count(*) FROM cs_Posts Posts2 WHERE Posts2.ParentID = Posts.PostID AND Posts2.IsApproved=1) CommentCount
	FROM 
		cs_Posts Posts,
		cs_Threads Thread,
		cs_ResourceLinks_Post LinkedTbl,
		#SearchResults R
	WHERE
		LinkedTbl.PostID = Posts.PostID AND
		Posts.PostID = R.PostID AND
		Thread.ThreadID = Posts.ThreadID AND
		R.IndexID > @PageLowerBound AND
		R.IndexID < @PageUpperBound AND
		Posts.SettingsID = @SettingsID

	-- Do we need to return a record estimate?
	DROP Table #SearchResults

	Select @TotalRecords

	SELECT Duration = GetDate() - @StartTime
END

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLink_Search] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResourceLinks_Post]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[cs_ResourceLinks_Post]
(
	[PostID] [int] NOT NULL,
	[LinkURL] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
	[ReportedDead] [bit] NULL DEFAULT (0),
	[ReportingUserID] [int] NULL DEFAULT (-1),
	[Rating] [int] NOT NULL DEFAULT (-1),
	PRIMARY KEY CLUSTERED 
	(
		[PostID] ASC
	)
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLink_Search_PostReindex]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[cs_ResLink_Search_PostReindex]
(
	@RowCount int,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

SET ROWCOUNT @RowCount

SELECT
	Posts.UserID , Thread.SectionID , Posts.PostID , Posts.TotalViews , Posts.ThreadID , Posts.Subject , Posts.Body , Posts.PostAuthor , Posts.PostDate , Posts.FormattedBody , Posts.IsApproved , LinkedTbl.LinkURL , LinkedTbl.ReportedDead , LinkedTbl.ReportingUserID ,
	Posts.PropertyNames , Posts.PropertyValues , Thread.MostRecentPostAuthor , Thread.MostRecentPostAuthorID , Thread.RatingSum , Thread.TotalRatings , 
	(SELECT Count(*) FROM cs_Posts Posts2 WHERE Posts2.ParentID = Posts.PostID AND Posts2.IsApproved=1) CommentCount,
	(SELECT Count(*) FROM cs_Posts Posts3 WHERE Posts3.ParentID = Posts.PostID AND Posts3.IsApproved=0) as CommentModerationCount	
FROM 
	cs_Posts Posts (nolock),
	cs_Threads Thread,
	cs_Sections F,
	cs_ResourceLinks_Post LinkedTbl
WHERE 
	LinkedTbl.PostID = Posts.PostID AND
	F.SectionID = Posts.SectionID AND
	F.IsActive = 1 AND
	F.IsSearchable = 1 AND 
	Posts.IsApproved = 1 AND Thread.ThreadID = Posts.ThreadID AND F.IsSearchable = 1 and 
	Posts.IsIndexed = 0 and F.SettingsID = @SettingsID and F.ApplicationType = 100 AND Posts.PostLevel = 1
ORDER BY
	Thread.ThreadDate DESC

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLink_Search_PostReindex] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkSection_GetLinks]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[cs_ResLinkSection_GetLinks]
(
	@SettingsID int,
	@SectionID int,
	@ViewUnmoderated bit,
	@ViewDead bit
)

as
SET Transaction Isolation Level Read UNCOMMITTED

if @ViewUnmoderated = 0
SELECT 
	Posts.UserID , Posts.TotalViews, Thread.SectionID , Posts.PostID , Posts.TotalViews , Posts.ThreadID , Posts.Subject , Posts.Body , Posts.PostAuthor , Posts.PostDate , Posts.FormattedBody , Posts.IsApproved , LinkedTbl.LinkURL , LinkedTbl.ReportedDead , LinkedTbl.ReportingUserID ,
	Posts.PropertyNames , Posts.PropertyValues , Thread.MostRecentPostAuthor , Thread.MostRecentPostAuthorID , Thread.RatingSum , Thread.TotalRatings , 
	(SELECT Count(*) FROM cs_Posts Posts2 WHERE Posts2.ParentID = Posts.PostID AND Posts2.IsApproved=1) as CommentCount,
	(SELECT Count(*) FROM cs_Posts Posts3 WHERE Posts3.ParentID = Posts.PostID AND Posts3.IsApproved=0) as CommentModerationCount
FROM 
	cs_Posts Posts
	JOIN cs_Threads Thread ON Thread.ThreadID = Posts.ThreadID
	JOIN cs_ResourceLinks_Post LinkedTbl ON  LinkedTbl.PostID = Posts.PostID
Where Posts.SectionID = @SectionID and Posts.SettingsID = @SettingsID  and Posts.IsApproved = 1 AND ( @ViewDead = ReportedDead OR ReportedDead = 0) AND PostType=0
else
SELECT 
	Posts.UserID , Posts.TotalViews,Thread.SectionID , Posts.PostID , Posts.TotalViews , Posts.ThreadID , Posts.Subject , Posts.Body , Posts.PostAuthor , Posts.PostDate , Posts.FormattedBody , Posts.IsApproved , LinkedTbl.LinkURL , LinkedTbl.ReportedDead , LinkedTbl.ReportingUserID ,
	Posts.PropertyNames , Posts.PropertyValues , Thread.MostRecentPostAuthor , Thread.MostRecentPostAuthorID , Thread.RatingSum , Thread.TotalRatings,
	(SELECT Count(*) FROM cs_Posts Posts2 WHERE Posts2.ParentID = Posts.PostID) as CommentCount,
	(SELECT Count(*) FROM cs_Posts Posts3 WHERE Posts3.ParentID = Posts.PostID AND Posts3.IsApproved=0) as CommentModerationCount
FROM 
	cs_Posts Posts
	JOIN cs_Threads Thread ON Thread.ThreadID = Posts.ThreadID
	JOIN cs_ResourceLinks_Post LinkedTbl ON  LinkedTbl.PostID = Posts.PostID
Where Posts.SectionID = @SectionID and Posts.SettingsID = @SettingsID  AND ( @ViewDead = ReportedDead OR ReportedDead = 0) AND Posts.PostType=0

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkSection_GetLinks] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkSection_GetLink]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[cs_ResLinkSection_GetLink]
(
	@SettingsID int,
	@SectionID int,
	@PostID int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

SELECT 
	Posts.UserID , Posts.TotalViews , Thread.SectionID , Posts.PostID , Posts.TotalViews , Posts.ThreadID , Posts.Subject , Posts.Body , Posts.PostAuthor , Posts.PostDate , Posts.FormattedBody , Posts.IsApproved , LinkedTbl.LinkURL , LinkedTbl.ReportedDead , LinkedTbl.ReportingUserID ,
	Posts.PropertyNames , Posts.PropertyValues , Thread.MostRecentPostAuthor , Thread.MostRecentPostAuthorID , Thread.RatingSum , Thread.TotalRatings,
	(SELECT Count(*) FROM cs_Posts Posts2 WHERE Posts2.ParentID = Posts.PostID) as CommentCount,
	(SELECT Count(*) FROM cs_Posts Posts3 WHERE Posts3.ParentID = Posts.PostID AND Posts3.IsApproved=0) as CommentModerationCount
FROM 
	cs_Posts Posts
	JOIN cs_Threads Thread ON Thread.ThreadID = Posts.ThreadID
	JOIN cs_ResourceLinks_Post LinkedTbl ON  LinkedTbl.PostID = Posts.PostID
Where Posts.SectionID = @SectionID and Posts.SettingsID = @SettingsID  AND Posts.PostID = @PostID
' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkSection_GetLink] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinks_GetAllDead]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[cs_ResLinks_GetAllDead]
(
	@SettingsId int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

SELECT     *, cs_ResourceLinks_Post.*
FROM         cs_Posts INNER JOIN
                      cs_ResourceLinks_Post ON cs_Posts.PostID = cs_ResourceLinks_Post.PostID AND ReportedDead=1
WHERE     (cs_Posts.SettingsID = @SettingsId)

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinks_GetAllDead] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkMarkDead]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[cs_ResLinkMarkDead]
(
	@PostID int,
	@UserID int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

UPDATE cs_ResourceLinks_Post SET ReportedDead = 1 , ReportingUserID = @UserID WHERE PostID = @PostId

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkMarkDead] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkSection_GetLinkComments]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[cs_ResLinkSection_GetLinkComments]
(
	@ParentPostID int,
	@SectionID int,
	@SettingsID int,
	@ViewUnmoderated bit
)

as
SET Transaction Isolation Level Read UNCOMMITTED


if @ViewUnmoderated = 0
SELECT 
	UserID,PostAuthor,Body,LinkedTbl.Rating,Posts.PostDate,Posts.PostID,Posts.IsApproved,Posts.ParentID,
	Posts.SectionID, Posts.FormattedBody
FROM 
	cs_Posts Posts,
	cs_ResourceLinks_Post LinkedTbl
Where SectionID = @SectionID and SettingsID = @SettingsID and IsApproved = 1 AND LinkedTbl.PostID = Posts.PostID AND  ParentID = @ParentPostID AND PostType=1
else
SELECT 
	UserID,PostAuthor,Body,LinkedTbl.Rating,Posts.PostDate,Posts.PostID,Posts.IsApproved,Posts.ParentID,
	Posts.SectionID, Posts.FormattedBody
FROM 
	cs_Posts Posts,
	cs_ResourceLinks_Post LinkedTbl
Where SectionID = @SectionID and SettingsID = @SettingsID AND LinkedTbl.PostID = Posts.PostID AND  ParentID = @ParentPostID AND PostType=1

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkSection_GetLinkComments] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkDeadLinksSummary]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[cs_ResLinkDeadLinksSummary]
(
	@SettingsId int,
	@ApplicationType int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

SELECT count(*) as LinkCount , 0 as CommentCount , cs_Sections.SectionID, cs_Sections.Name
	FROM   cs_Posts INNER JOIN
			cs_Sections ON cs_Posts.SettingsID = cs_Sections.SettingsID AND cs_Posts.SectionID = cs_Sections.SectionID
			JOIN cs_ResourceLinks_Post LinkedTbl ON  LinkedTbl.PostID = cs_Posts.PostID
	WHERE @SettingsId = cs_Sections.SettingsID AND cs_Sections.ApplicationType = @ApplicationType AND cs_Posts.PostType=0 AND LinkedTbl.ReportedDead > 0
	GROUP BY cs_Sections.SectionID, cs_Sections.Name

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLink_Sections_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[cs_ResLink_Sections_GetAll]
(
	@SettingsID	int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

SELECT 
	SectionID, SettingsID, ParentID, [Name],  [Description] , ApplicationKey ,PropertyNames , PropertyValues , IsActive , IsModerated , ApplicationType
FROM
	cs_Sections 
WHERE
	SettingsID = @SettingsID AND
	ApplicationType = 100


' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLink_Sections_GetAll] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkBreadCrumbGet]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[cs_ResLinkBreadCrumbGet]
(
	@SettingsID int,
	@SectionID int,
	@ApplicationType smallint
)
as
SET Transaction Isolation Level Read UNCOMMITTED

CREATE TABLE #Trail 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	SectionParentID int NOT NULL
)

DECLARE @localParentID  as int
DECLARE @prevParentParentID as  int

SET @localParentID = 0

SELECT @localParentID = ParentID FROM cs_Sections WITH (nolock) WHERE  ApplicationType = @ApplicationType and SettingsID = @SettingsID and SectionID = @SectionID  

INSERT INTO #Trail (#Trail.SectionParentID) VALUES ( @localParentID )

while @localParentID > 0
begin 
	set @prevParentParentID = @localParentID
	
	SELECT @localParentID =ParentID
	FROM cs_Sections (nolock)
	WHERE  ApplicationType = @ApplicationType and SettingsID = @SettingsID and SectionID = @prevParentParentID  
	
	INSERT INTO #Trail (SectionParentID) VALUES ( @localParentID )
end

SELECT cs_Sections.SectionID , cs_Sections.ParentID , cs_Sections.[Name], cs_Sections.[Description] FROM #Trail , cs_Sections WHERE #Trail.SectionParentID =  cs_Sections.SectionID

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkBreadCrumbGet] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkUpdateLinkHitCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[cs_ResLinkUpdateLinkHitCount]
(
	@PostId int,
	@UserId int,
	@SectionId int,
	@SettingsId int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

UPDATE cs_Posts SET TotalViews = (TotalViews + 1) WHERE PostID = @PostID and SettingsID = @SettingsID
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkMoveLink]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[cs_ResLinkMoveLink]
(
	@PostId int,
	@UserId int,
	@SectionId int,
	@SettingsId int,
	@NewSectionId int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

	Declare @ApplicationKey nvarchar(256)
	
	/*if we are updating then lets add a moderation action*/
	Select @ApplicationKey = ''Moved Reource Link '' + ApplicationKey FROM cs_Sections where SectionID = @SectionId and SettingsID = @SettingsId

	DECLARE @linkOwner int
	
	SELECT @linkOwner = UserID FROM cs_Posts WHERE @PostId = PostID AND SectionID = @SectionId and SettingsID = @SettingsId
	
	exec cs_system_ModerationAction_AuditEntry  102 , @linkOwner , @PostId , @UserID, @SectionId, @SettingsId,  @ApplicationKey

	declare @threadID int
	
	SELECT @threadID = ThreadID FROM cs_Posts where PostID = @PostId  AND SettingsID = @SettingsID AND SectionID = @SectionId
	
	UPDATE cs_Posts SET SectionId = @NewSectionId 
		WHERE (PostID = @PostId OR ParentID=@PostId)  AND SettingsID = @SettingsID AND SectionID = @SectionId

	UPDATE cs_Threads SET SectionId = @NewSectionId 
		WHERE SettingsID = @SettingsID AND SectionID = @SectionId AND ThreadID = @threadID

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkMoveLink] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkLink_InsertUpdate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[cs_ResLinkLink_InsertUpdate]
(
	@PostId int out,
	@SectionId int,
	@SettingsId int,
	@UserId int,
	@Title nvarchar(50),
	@Body		ntext,
	@FormattedBody	ntext,
	@IsApproved bit = 0,
	@ReportedDead bit = 0,
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@UserHostAddress nvarchar(32),
	@LinkURL nvarchar(255)
)

as
SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @DateNow datetime

SELECT @DateNow = GETDATE()
 
declare @PostAuthor nvarchar(64)

SELECT 
	@PostAuthor = UserName
FROM 
	cs_vw_Users_FullUser  
WHERE 
	cs_UserID = @UserID and SettingsID = @SettingsID	
		
 /*create*/
if @PostId > 0
begin

	Declare @ThreadID int
	Select @ThreadID = ThreadID FROM cs_Posts where PostID = @PostId

	Declare @ApplicationKey nvarchar(256)
	
	/*if we are updating then lets add a moderation action*/
	Select @ApplicationKey = ''Updated Reource Link '' + ApplicationKey FROM cs_Sections where SectionID = @SectionId and SettingsID = @SettingsId

	DECLARE @linkOwner int
	
	SELECT @linkOwner = UserID FROM cs_Posts WHERE @PostId = PostID AND SectionID = @SectionId and SettingsID = @SettingsId
	
	exec cs_system_ModerationAction_AuditEntry  100 , @UserID , @PostId ,@linkOwner, @SectionId, @SettingsId,  @ApplicationKey

	UPDATE cs_Posts SET PostDate = @DateNow , Subject = @Title, Body = @Body , FormattedBody = @FormattedBody , IsApproved = @IsApproved , PropertyNames = @PropertyNames, PropertyValues = @PropertyValues 
		WHERE PostID = @PostId  AND SettingsID = @SettingsID AND SectionID = @SectionId


	UPDATE cs_ResourceLinks_Post SET LinkURL = @LinkURL , ReportedDead = @ReportedDead WHERE PostID = @PostId
	
end
else  
begin
	/*Create a new thread*/
	INSERT cs_Threads 	
		( SectionID,
		PostDate, 
		UserID, 
		PostAuthor, 
		ThreadDate, 
		MostRecentPostAuthor, 
		MostRecentPostAuthorID, 	
		MostRecentPostID, 
		IsLocked, 
		IsApproved,
		IsSticky, 
		StickyDate, 
		ThreadEmoticonID,
		SettingsID )
	VALUES
		( @SectionID, 
		@DateNow, 
		@UserID, 
		@PostAuthor,
		@DateNow,
		@PostAuthor,
		@UserID, 
		0,	-- MostRecentPostID, which we don''t know until after post INSERT below.
		0,  --cant lock links
		@IsApproved,
		0, --Not sticky
		@DateNow, -- no sticky date
		0, --no icons
		@SettingsID )

	-- Get the new ThreadID
	SELECT @ThreadID = @@IDENTITY
	
	/*Now insert a post*/
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 
		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID
	        )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		0, 	-- ParentID, always 0
		1, 	-- PostLevel, 1 marks start/top/first post in thread.
		1, 	-- SortOrder (not in use at this time)
		@Title, 
		@UserID, 
		@PostAuthor, --No post author
		@IsApproved, 
		0, 
		@Body, 
		@FormattedBody, 
		0, 
		@DateNow, 
		@UserHostAddress, 
		0,
		@PropertyNames,
		@PropertyValues,
		@SettingsID )
		
	-- Get the new PostID
	SELECT @PostId = @@Identity
	
	INSERT  cs_ResourceLinks_Post (PostID , LinkURL , ReportedDead) VALUES(@PostId , @LinkURL , @ReportedDead)

	UPDATE 
		cs_Threads
	SET 
		MostRecentPostID = @PostId
	WHERE 
		ThreadID = @ThreadID and SettingsID = @SettingsID	
	
end

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkLink_InsertUpdate] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkComment_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[cs_ResLinkComment_Delete]
(
	@PostId int,
	@ParentId int,
	@SectionId int,
	@UserId int,
	@SettingsID int,
	@Reason nvarchar(250)
)

as
SET Transaction Isolation Level Read UNCOMMITTED
	
	DECLARE @PreviousApproved bit
	DECLARE @linkOwner int
	SELECT @PreviousApproved = IsApproved  , @linkOwner = UserID FROM cs_Posts WHERE @PostId = PostID AND SectionID = @SectionId and SettingsID = @SettingsID

	exec cs_system_ModerationAction_AuditEntry  103 , @linkOwner , @PostId , @UserID, @SectionId, @SettingsID,  @Reason

	DECLARE @Rating int	

	Select @Rating = Rating FROM cs_ResourceLinks_Post WHERE @PostId = PostID
	
	/*Only correct the rating if its been applied*/
	if @Rating > -1 AND @PreviousApproved > 0
	BEGIN
		/* get any rating information and remove it from the threads table correcting the average rating*/
		Declare @ThreadID int
		Select @ThreadID = ThreadID FROM cs_Posts where PostID = @PostId
		
		Declare @TotalRatings int
		Declare @RatingsSum int
		
		Select @ThreadID = ThreadID , @TotalRatings = TotalRatings , @RatingsSum = RatingSum  FROM cs_Threads where ThreadID = @ThreadID	
		
		SET @TotalRatings = @TotalRatings - 1
		SET  @RatingsSum =  @RatingsSum - @Rating
		
		UPDATE 
			cs_Threads
		SET 
			RatingSum = @RatingsSum ,
			TotalRatings = @TotalRatings
		WHERE 
			ThreadID = @ThreadID and SettingsID = @SettingsID	
	END
	
	DELETE FROM cs_Posts WHERE PostID = @PostId AND SettingsID = @SettingsID AND ParentID = @ParentId

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkComment_Delete] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkComment_InsertUpdate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[cs_ResLinkComment_InsertUpdate]
(
	@PostId int out,
	@SectionId int,
	@SettingsID int,
	@UserId int,
	@Body		ntext,
	@FormattedBody	ntext,
	@IsApproved bit,
	@UserHostAddress nvarchar(32),
	@Rating int,
	@ParentID int 
)

as
SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @DateNow datetime

SELECT @DateNow = GETDATE()
 
declare @PostAuthor nvarchar(64)

SELECT 
	@PostAuthor = UserName
FROM 
	cs_vw_Users_FullUser  
WHERE 
	cs_UserID = @UserID and SettingsID = @SettingsID	
	
Declare @ThreadID int
Select @ThreadID = ThreadID FROM cs_Posts where PostID = @ParentID

DECLARE @ratingSum int
DECLARE @totalRatings int 

if @ThreadID > 0
BEGIN

	/*create*/
	if @PostId > 0
	begin
		Declare @ApplicationKey nvarchar(256)		
		/*if we are updating then lets add a moderation action*/
		Select @ApplicationKey = ''Updated Reource Link Comment '' + ApplicationKey FROM cs_Sections where SectionID = @SectionId and SettingsID = @SettingsID


		/*Get the old rating and delete it of of the rating totals this is a bit of a cludge*/
		DECLARE @PreviousRating int
		SELECT @PreviousRating = Rating FROM cs_ResourceLinks_Post WHERE PostID = @PostId 

		/*Get the old approved flag*/
		DECLARE @PreviousApproved bit
		DECLARE @linkOwner int
		SELECT @PreviousApproved = IsApproved  , @linkOwner = UserID FROM cs_Posts WHERE @PostId = PostID AND SectionID = @SectionId and SettingsID = @SettingsID
		
		exec cs_system_ModerationAction_AuditEntry  104 , @linkOwner , @PostId , @UserID, @SectionId, @SettingsID,  @ApplicationKey

		UPDATE cs_Posts SET Body = @Body , FormattedBody = @FormattedBody , IsApproved = @IsApproved
			WHERE PostID = @PostId  AND SettingsID = @SettingsID AND SectionID = @SectionId

		UPDATE cs_ResourceLinks_Post SET Rating = @Rating WHERE PostID = @PostId
		
		SELECT @ratingSum = RatingSum , @totalRatings = TotalRatings FROM cs_Threads WHERE ThreadID = @ThreadID and SettingsID = @SettingsID;
	
		if (@PreviousApproved = 0)
		BEGIN
			if @Rating >= 0 AND @IsApproved = 1
			BEGIN
				set @ratingSum = @ratingSum + @Rating
				set @totalRatings = @totalRatings + 1
			END
		END
		else 
		BEGIN
			if @PreviousRating > -1 /*If the thing had been rated before and was approved*/
			BEGIN
				set @ratingSum = @ratingSum + @Rating
				set @ratingSum = @ratingSum - @PreviousRating				
			END
			else
			BEGIN
				if @Rating >= 0 AND @IsApproved = 1
				BEGIN
					set @ratingSum = @ratingSum + @Rating
					set @totalRatings = @totalRatings + 1				
				END			
			END
		END	
		
		UPDATE 
			cs_Threads
		SET 
			MostRecentPostID = @PostId,
			MostRecentPostAuthorID = @UserId,
			RatingSum = @ratingSum ,
			TotalRatings = @totalRatings
		WHERE 
			ThreadID = @ThreadID and SettingsID = @SettingsID	

	end
	else  
	begin
		/*Now insert a post*/
		INSERT cs_Posts 
			(	SectionID, 
				ThreadID, 
				ParentID,
				SortOrder,  
				PostLevel, 
				UserID, 
				PostAuthor, 
				IsApproved, 
				Body, 
				FormattedBody, 
				PostType, 
				PostDate, 
				IPAddress, 
				SettingsID
				)
		VALUES 
			( @SectionID, 
			@ThreadID, 
			@ParentID,
			2,
			2, 	-- PostLevel, 1 marks start/top/first post in thread.
			@UserID, 
			@PostAuthor, --No post author
			@IsApproved, 
			@Body, 
			@FormattedBody, 
			1, 
			@DateNow, 
			@UserHostAddress, 
			@SettingsID )
			
		-- Get the new PostID
		SELECT @PostId = @@Identity
		
		INSERT  cs_ResourceLinks_Post (PostID  , LinkURL , Rating) VALUES(@PostId , '''' , @Rating)

		if @Rating >= 0 AND @IsApproved = 1
		BEGIN
			
			SELECT @ratingSum = RatingSum , @totalRatings = TotalRatings FROM cs_Threads WHERE ThreadID = @ThreadID and SettingsID = @SettingsID;

			set @ratingSum = @ratingSum + @Rating
			set @totalRatings = @totalRatings + 1
				
			UPDATE 
				cs_Threads
			SET 
				MostRecentPostID = @PostId,
				MostRecentPostAuthorID = @UserId,
				RatingSum = @ratingSum ,
				TotalRatings = @totalRatings
			WHERE 
				ThreadID = @ThreadID and SettingsID = @SettingsID	
		END
	end


	
END

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkComment_InsertUpdate] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkEmails_TrackingSection]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[cs_ResLinkEmails_TrackingSection]
(
	@SettingsID int,
	@SectionID int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

		SELECT
			U.Email, 
			U.EnableHtmlEmail
		FROM 
			cs_TrackedSections F
			JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = F.UserID
		WHERE
			F.SectionID = @SectionID AND F.SettingsID = @SettingsID and U.SettingsID = @SettingsID
			and F.SubscriptionType <> 0

' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkEmails_TrackingSection] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkLink_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[cs_ResLinkLink_Delete]
(
	@PostID int,
	@SectionId int,
	@UserId int,
	@SettingsId int
)

as
SET Transaction Isolation Level Read UNCOMMITTED

	Declare @ApplicationKey nvarchar(256)
	
	/*if we are updating then lets add a moderation action*/
	Select @ApplicationKey = ''Deleted Reource Link '' + ApplicationKey FROM cs_Sections where SectionID = @SectionId and SettingsID = @SettingsId

	DECLARE @linkOwner int
	
	SELECT @linkOwner = UserID FROM cs_Posts WHERE @PostID = PostID AND SectionID = @SectionId and SettingsID = @SettingsId

	exec cs_system_ModerationAction_AuditEntry  101 , @UserID , @PostID , @linkOwner , @SectionId, @SettingsId,  @ApplicationKey

	DELETE FROM cs_ResourceLinks_Post WHERE PostID = @PostID
	DELETE FROM cs_Posts WHERE (PostID = @PostID or ParentID = @PostID)AND SettingsID = @SettingsID
' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkLink_Delete] TO [public]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkSection_Get]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[cs_ResLinkSection_Get]
(
	@SettingsID int,
	@SectionID int,
	@ApplicationType smallint
)

as
SET Transaction Isolation Level Read UNCOMMITTED

IF @SectionID < 0  
BEGIN
	/* Get this sections details */
	SELECT 
		@SectionID  = SectionID
	FROM 
		cs_Sections 
	Where ParentID = 0 and ApplicationType = @ApplicationType and SettingsID = @SettingsID 	
	
	set @SectionID = 0
END

/* Get this sections details */
SELECT 
	SectionID, SettingsID, ParentID, [Name],  [Description] , ApplicationKey ,PropertyNames , PropertyValues , IsActive , IsModerated , ApplicationType
FROM 
	cs_Sections 
Where SectionID = @SectionID and ApplicationType = @ApplicationType and SettingsID = @SettingsID 


/*Get this sections security*/
exec cs_Section_Permissions_Get @SettingsID  ,  @SectionID , @ApplicationType

/*Now select the child sections*/
SELECT 
	SectionID  ,[Name] , IsActive ,  ApplicationKey 
FROM 
	cs_Sections 
Where ParentID = @SectionID and ApplicationType = @ApplicationType and SettingsID = @SettingsID 
' 
END
GO
GRANT EXECUTE ON [dbo].[cs_ResLinkSection_Get] TO [public]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_cs_ResourceLinks_Post]')) --2005 thing AND parent_id = OBJECT_ID(N'[dbo].[cs_ResourceLinks_Post]'))
ALTER TABLE [dbo].[cs_ResourceLinks_Post]  WITH CHECK ADD  CONSTRAINT [FK_cs_ResourceLinks_Post] FOREIGN KEY([PostID])
REFERENCES [dbo].[cs_Posts] ([PostID])
ON DELETE CASCADE

--Finally we create our initialisation script
Print 'Creating...cs_system_FourRoadsLinksExtensions'

go
if exists (select * from dbo.sysobjects where id = OBJECT_ID(N'[dbo].[cs_system_FourRoadsLinksExtensions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_system_FourRoadsLinksExtensions]
GO

create Proc [dbo].cs_system_FourRoadsLinksExtensions
(
	@ApplicationName nvarchar(512)
)

as

Declare @ApplicationID uniqueidentifier

--Get or Create the Application ID.
--An ApplicationName/ID can exist in more than one site, so it does not need to be unique
Set @ApplicationID = null
Select @ApplicationID = ApplicationId FROM aspnet_Applications where  LoweredApplicationName = Lower(@ApplicationName)

Declare @SettingsID int
Select @SettingsID = SettingsId FROM cs_SiteSettings where  ApplicationName = @ApplicationName

INSERT INTO cs_ModerationAction (ModerationAction, [Description], TotalActions, SettingsID) VALUES (100, 'Update Resource Link', 0, @SettingsID)
INSERT INTO cs_ModerationAction (ModerationAction, [Description], TotalActions, SettingsID) VALUES (101, 'Deleted Resource Link', 0, @SettingsID)
INSERT INTO cs_ModerationAction (ModerationAction, [Description], TotalActions, SettingsID) VALUES (102 , 'Moved Resource Link', 0, @SettingsID)

INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N'Link Administrator', N'link administrator', N'Administrator for all resource link pages')


declare @roleID uniqueidentifier

Select @roleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'Link Administrator'

INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 100, @roleID, 0x00000000000FFFFF, 0x0000000000000000 )

Select @roleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'Everyone'

INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 100, @roleID, 0x0000000000000001, 0x0000000000000000 )

Select @roleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'Registered Users'

INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 100, @roleID, 0x0000000000000003, 0x0000000000000000 )

Select @roleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'Moderator'

INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 100, @roleID, 0x000000000000004F, 0x0000000000000000 )

Select @roleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = 'SystemAdministrator'

INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 100, @roleID, 0x00000000000FFFFF, 0x0000000000000000 )

go

if not exists( select * from cs_ApplicationType where ApplicationType = 100 )
						insert into cs_ApplicationType values( 100, 'ResLink')
go

grant execute on [dbo].[cs_system_FourRoadsLinksExtensions] to public
go
